Cleaning

At this stage, we use the file we had saved in the input_data folder to create datetime variables,

[2]:
use "..\input_data\NYPD_Motor_Vehicle_Collisions",clear

Notice that we used ..\ here again. When this jupyter notebook is opened, its current directory is in the “dofiles” folder. Therefore, we use the relative paths to “move” to the “input_data” to get the dataset.

[3]:
notes list

_dta:
  1.  "Downloaded 9 Dec 2018 11:30:26"
[4]:
%browse
date time borough zipcode latitude longitude location onstreetname crossstreetname offstreetname numberofpersonsinjured numberofpersonskilled numberofpedestriansinjured numberofpedestrianskilled numberofcyclistinjured numberofcyclistkilled numberofmotoristinjured numberofmotoristkilled contributingfactorvehicle1 contributingfactorvehicle2 contributingfactorvehicle3 contributingfactorvehicle4 contributingfactorvehicle5 uniquekey vehicletypecode1 vehicletypecode2 vehicletypecode3 vehicletypecode4 vehicletypecode5
1 11/16/2018 0:10 MANHATTAN 10010 40.742275 -73.988914 (40.742275, -73.988914) 5 AVENUE BROADWAY . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021880 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
2 11/16/2018 0:40 . . 40.762718 -73.728172 (40.76272, -73.72817) LONG ISLAND EXPRESSWAY . . 0 0 0 0 0 0 0 0 Pavement Slippery Pavement Slippery . . . 4021727 Sedan Pick-up Truck . . .
3 11/16/2018 0:45 BROOKLYN 11211 40.710197 -73.958427 (40.710197, -73.95843) BORINQUEN PLACE HAVEMEYER STREET . 4 0 0 0 0 0 4 0 Driver Inattention/Distraction Driver Inattention/Distraction . . . 4021791 Taxi Taxi . . .
4 11/16/2018 1:00 BRONX 10454 40.803555 -73.911842 (40.803555, -73.91184) EAST 137 STREET WILLOW AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021878 Garbage or Refuse Station Wagon/Sport Utility Vehicle . . .
5 11/16/2018 1:00 BROOKLYN 11221 40.694923 -73.915565 (40.694923, -73.915565) WILSON AVENUE PALMETTO STREET . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021848 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
6 11/16/2018 1:20 . . 40.804699 -73.91243 (40.8047, -73.91243) EAST 138 STREET BRUCKNER BOULEVARD . 1 0 1 0 0 0 0 0 . . . . . 4021877 . . . . .
7 11/16/2018 1:41 . . 40.737682 -73.852058 (40.737682, -73.85206) 108 STREET HORACE HARDING EXPRESSWAY . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021798 Sedan Sedan . . .
8 11/16/2018 2:50 BRONX 10451 40.819057 -73.92923 (40.819057, -73.92923) EAST 149 STREET GERARD AVENUE . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021864 Taxi Taxi . . .
9 11/16/2018 4:22 . . 40.811749 -73.931442 (40.81175, -73.93144) MAJOR DEEGAN EXPRESSWAY . . 0 0 0 0 0 0 0 0 Unsafe Speed Unspecified Unspecified . . 4021857 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
10 11/16/2018 5:30 QUEENS 11417 40.678871 -73.83419 (40.67887, -73.83419) ROCKAWAY BOULEVARD CENTREVILLE AVENUE . 1 0 1 0 0 0 0 0 Driver Inattention/Distraction . . . . 4021833 . . . . .
11 11/16/2018 5:45 MANHATTAN 10128 40.779179 -73.950798 (40.77918, -73.9508) EAST 88 STREET 2 AVENUE . 0 0 0 0 0 0 0 0 Unspecified . . . . 4021904 Station Wagon/Sport Utility Vehicle . . . .
12 11/16/2018 6:00 . . 40.743149 -73.831924 (40.74315, -73.831924) LONG ISLAND EXPRESSWAY . . 0 0 0 0 0 0 0 0 Passing or Lane Usage Improper Passing or Lane Usage Improper . . . 4021865 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
13 11/16/2018 6:02 BROOKLYN 11206 40.700001 -73.940643 (40.7, -73.94064) . . 787 BROADWAY 0 0 0 0 0 0 0 0 Following Too Closely Driver Inattention/Distraction . . . 4021892 Garbage or Refuse Sedan . . .
14 11/16/2018 6:10 MANHATTAN 10128 40.781689 -73.948967 (40.78169, -73.94897) EAST 92 STREET 2 AVENUE . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021859 Sedan Sedan . . .
15 11/15/2018 0:00 BROOKLYN 11228 40.620872 -74.002213 (40.620872, -74.00221) . . 1421 71 STREET 0 0 0 0 0 0 0 0 Failure to Yield Right-of-Way Alcohol Involvement Unspecified . . 4021704 Sedan Sedan Sedan . .
16 11/15/2018 0:00 QUEENS 11004 40.752037 -73.7211 (40.752037, -73.7211) LITTLE NECK PARKWAY 260 STREET . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021596 Station Wagon/Sport Utility Vehicle Sedan . . .
17 11/15/2018 0:00 QUEENS 11368 40.749889 -73.862587 (40.74989, -73.86259) . . 103-03 ROOSEVELT AVENUE 0 0 0 0 0 0 0 0 Passing or Lane Usage Improper Unspecified . . . 4021503 Sedan Station Wagon/Sport Utility Vehicle . . .
18 11/15/2018 0:00 QUEENS 11375 40.716141 -73.833572 (40.71614, -73.83357) QUEENS BOULEVARD 77 AVENUE . 0 0 0 0 0 0 0 0 Traffic Control Disregarded Unspecified Unspecified . . 4021235 Sedan Sedan Sedan . .
19 11/15/2018 0:00 . . 40.649269 -74.009674 (40.64927, -74.009674) 45 STREET . . 0 0 0 0 0 0 0 0 Unspecified . . . . 4021611 Sedan . . . .
20 11/15/2018 0:00 . . 40.610256 -74.097298 (40.610256, -74.0973) CLOVE ROAD EMERSON DRIVE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021736 Station Wagon/Sport Utility Vehicle Sedan . . .
21 11/15/2018 0:00 . . 40.710419 -73.859642 (40.71042, -73.85964) WOODHAVEN BOULEVARD . . 0 0 0 0 0 0 0 0 Following Too Closely Reaction to Uninvolved Vehicle . . . 4021629 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
22 11/15/2018 0:00 QUEENS 11413 40.666405 -73.745377 (40.666405, -73.74538) NORTH CONDUIT AVENUE 230 PLACE . 1 0 0 0 0 0 1 0 Pavement Slippery . . . . 4021563 Station Wagon/Sport Utility Vehicle . . . .
23 11/15/2018 0:17 BROOKLYN 11204 40.610115 -73.984894 (40.610115, -73.98489) AVENUE O WEST 11 STREET . 0 0 0 0 0 0 0 0 Backing Unsafely Unspecified . . . 4020952 Sedan Station Wagon/Sport Utility Vehicle . . .
24 11/15/2018 0:41 . . 40.672062 -73.925255 (40.672062, -73.925255) PARK PLACE . . 0 0 0 0 0 0 0 0 Passing or Lane Usage Improper Unspecified . . . 4020993 Sedan Convertible . . .
25 11/15/2018 0:46 . . 40.761856 -73.963432 (40.761856, -73.96343) EAST 61 STREET . . 0 0 0 0 0 0 0 0 Driver Inexperience Unspecified . . . 4021088 Station Wagon/Sport Utility Vehicle Sedan . . .
26 11/15/2018 10:00 BROOKLYN 11230 40.626839 -73.968483 (40.62684, -73.96848) AVENUE I EAST 8 STREET . 0 0 0 0 0 0 0 0 Failure to Yield Right-of-Way Unspecified . . . 4021808 Sedan Dump . . .
27 11/15/2018 10:00 QUEENS 11434 40.659649 -73.773834 (40.65965, -73.773834) ROCKAWAY BOULEVARD FARMERS BOULEVARD . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction . . . . 4021414 Sedan . . . .
28 11/15/2018 10:00 . . . . . South Elliott Place South Portland Ave . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021321 Box Truck Station Wagon/Sport Utility Vehicle . . .
29 11/15/2018 10:00 BROOKLYN 11229 40.604786 -73.958473 (40.604786, -73.95847) . . 1835 EAST 14 STREET 1 0 1 0 0 0 0 0 Backing Unsafely . . . . 4021389 Sedan . . . .
30 11/15/2018 10:09 QUEENS 11374 40.728657 -73.863266 (40.728657, -73.863266) BOOTH STREET 63 DRIVE . 0 0 0 0 0 0 0 0 Failure to Yield Right-of-Way Unspecified . . . 4021355 Station Wagon/Sport Utility Vehicle Sedan . . .
31 11/15/2018 10:10 QUEENS 11378 40.722717 -73.919914 (40.722717, -73.919914) . . 48-85 MASPETH AVENUE 0 0 0 0 0 0 0 0 Backing Unsafely Unspecified . . . 4021443 Tractor Truck Diesel Tanker . . .
32 11/15/2018 10:10 . . 40.856358 -73.826515 (40.856358, -73.826515) BRUCKNER EXPRESSWAY . . 1 0 0 0 0 0 1 0 Unsafe Lane Changing Unspecified . . . 4021293 Station Wagon/Sport Utility Vehicle Taxi . . .
33 11/15/2018 10:15 MANHATTAN 10036 40.764904 -73.996185 (40.764904, -73.996185) . . 621 WEST 48 STREET 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021372 Box Truck Station Wagon/Sport Utility Vehicle . . .
34 11/15/2018 10:30 BROOKLYN 11215 40.671921 -73.993973 (40.67192, -73.99397) . . 118 2 AVENUE 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified Unspecified . . 4021905 Station Wagon/Sport Utility Vehicle Sedan Sedan . .
35 11/15/2018 10:30 BROOKLYN 11230 40.62685 -73.976433 (40.62685, -73.97643) . . 1080 MC DONALD AVENUE 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021811 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
36 11/15/2018 10:32 . . 40.664642 -73.93708 (40.664642, -73.93708) TROY AVENUE . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021517 Station Wagon/Sport Utility Vehicle Sedan . . .
37 11/15/2018 10:35 BROOKLYN 11229 40.610119 -73.961105 (40.61012, -73.961105) . . 1220 AVENUE P 1 0 0 0 1 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021390 Bike Sedan . . .
38 11/15/2018 10:36 BRONX 10473 40.815006 -73.845253 (40.815006, -73.84525) ZEREGA AVENUE NORTON AVENUE . 0 0 0 0 0 0 0 0 Unsafe Lane Changing Unspecified . . . 4021419 Sedan Station Wagon/Sport Utility Vehicle . . .
39 11/15/2018 10:40 . . 40.65881 -73.98172 (40.65881, -73.98172) PROSPECT PARK WEST . . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021464 Station Wagon/Sport Utility Vehicle Dump . . .
40 11/15/2018 10:42 MANHATTAN 10036 40.754158 -73.982483 (40.754158, -73.98248) . . 49 WEST 42 STREET 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021508 Station Wagon/Sport Utility Vehicle . . . .
41 11/15/2018 10:43 MANHATTAN 10065 40.765411 -73.967979 (40.76541, -73.96798) PARK AVENUE EAST 63 STREET . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021834 Taxi Taxi . . .
42 11/15/2018 10:59 BROOKLYN 11215 40.674805 -73.978401 (40.674805, -73.9784) PRESIDENT STREET 6 AVENUE . 0 0 0 0 0 0 0 0 Backing Unsafely Unspecified . . . 4021881 Station Wagon/Sport Utility Vehicle Tractor Truck Diesel . . .
43 11/15/2018 11:00 . . 40.592815 -73.908081 (40.592815, -73.90808) BELT PARKWAY . . 0 0 0 0 0 0 0 0 Unsafe Lane Changing Unspecified . . . 4021306 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
44 11/15/2018 11:00 . . 40.741447 -73.846031 (40.741447, -73.84603) GRAND CENTRAL PKWY . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unsafe Lane Changing . . . 4021403 Sedan Sedan . . .
45 11/15/2018 11:00 . . 40.706306 -73.919586 (40.706306, -73.919586) SUYDAM STREET . . 0 0 0 0 0 0 0 0 Unspecified . . . . 4021858 Station Wagon/Sport Utility Vehicle . . . .
46 11/15/2018 11:02 QUEENS 11385 40.705936 -73.857338 (40.705936, -73.85734) . . 89-89 UNION TURNPIKE 0 0 0 0 0 0 0 0 Unspecified . . . . 4021433 Box Truck . . . .
47 11/15/2018 11:15 MANHATTAN 10036 . . . W 45 ST AVENUE OF THE AMERICAS . 0 0 0 0 0 0 0 0 Unsafe Lane Changing Unspecified . . . 4021374 Van Sedan . . .
48 11/15/2018 11:15 . . 40.60957 -73.819054 (40.60957, -73.81905) CROSS BAY BOULEVARD . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021437 Sedan Station Wagon/Sport Utility Vehicle . . .
49 11/15/2018 11:16 BRONX 10456 40.820156 -73.90834 (40.820156, -73.90834) . . 645 EAST 158 STREET 0 0 0 0 0 0 0 0 Failure to Yield Right-of-Way Unspecified . . . 4021359 Sedan Sedan . . .
50 11/15/2018 11:20 MANHATTAN 10035 40.806915 -73.93557 (40.806915, -73.93557) EAST 129 STREET LEXINGTON AVENUE . 0 0 0 0 0 0 0 0 Unspecified Unspecified Unspecified . . 4021469 Sedan Box Truck Box Truck . .
51 11/15/2018 11:21 BROOKLYN 11209 40.616089 -74.037308 (40.61609, -74.03731) . . 120 96 STREET 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021298 Sedan Sedan . . .
52 11/15/2018 11:25 QUEENS 11373 40.745033 -73.87001 (40.745033, -73.87001) . . 94-41 43 AVENUE 0 0 0 0 0 0 0 0 Following Too Closely Driver Inattention/Distraction . . . 4021363 Sedan Station Wagon/Sport Utility Vehicle . . .
53 11/15/2018 11:26 BRONX 10467 40.881577 -73.859116 (40.881577, -73.859116) . . 839 EAST 218 STREET 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021476 Station Wagon/Sport Utility Vehicle Bus . . .
54 11/15/2018 11:37 BROOKLYN 11220 40.647064 -74.022629 (40.647064, -74.02263) 56 STREET 1 AVENUE . 0 0 0 0 0 0 0 0 Unspecified . . . . 4021467 Station Wagon/Sport Utility Vehicle . . . .
55 11/15/2018 11:40 BRONX 10455 40.814823 -73.909531 (40.814823, -73.90953) PONTIAC PLACE TRINITY AVENUE . 0 0 0 0 0 0 0 0 Other Vehicular Unspecified . . . 4021292 Box Truck Sedan . . .
56 11/15/2018 11:40 MANHATTAN 10025 40.802345 -73.968117 (40.802345, -73.96812) WEST END AVENUE WEST 107 STREET . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021849 Sedan Taxi . . .
57 11/15/2018 11:42 . . . . . LAUREL HILL BOULEVARD 61 STREET . 0 0 0 0 0 0 0 0 Unsafe Lane Changing Unspecified . . . 4021444 Sedan Sedan . . .
58 11/15/2018 11:45 . . . . . 224 STREET BRADDOCK AVENUE . 0 0 0 0 0 0 0 0 Unspecified Unspecified Unspecified . . 4021412 Station Wagon/Sport Utility Vehicle Sedan Station Wagon/Sport Utility Vehicle . .
59 11/15/2018 11:50 BRONX 10470 40.89978 -73.867599 (40.89978, -73.8676) . . 4330 KATONAH AVENUE 0 0 0 0 0 0 0 0 Unspecified . . . . 4021475 Station Wagon/Sport Utility Vehicle . . . .
60 11/15/2018 11:50 . . 40.804699 -73.91243 (40.8047, -73.91243) EAST 138 STREET . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021862 Box Truck Sedan . . .
61 11/15/2018 12:00 BROOKLYN 11221 40.69595 -73.917389 (40.69595, -73.91739) . . 330 WILSON AVENUE 0 0 0 0 0 0 0 0 Unspecified . . . . 4021845 Sedan . . . .
62 11/15/2018 12:00 . . 40.770222 -73.830551 (40.77022, -73.83055) 138 STREET . . 0 0 0 0 0 0 0 0 Traffic Control Disregarded Unspecified . . . 4021601 Sedan Sedan . . .
63 11/15/2018 12:00 . . 40.741074 -73.7258 (40.741074, -73.7258) CROSS ISLAND PARKWAY UNION TURNPIKE . 0 0 0 0 0 0 0 0 Unsafe Lane Changing Unspecified . . . 4021413 Sedan Sedan . . .
64 11/15/2018 12:15 . . 40.759281 -73.959274 (40.75928, -73.959274) YORK AVENUE . . 0 0 0 0 0 0 0 0 Failure to Yield Right-of-Way Unspecified . . . 4021842 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
65 11/15/2018 12:15 BROOKLYN 11206 40.711643 -73.943916 (40.711643, -73.94392) GRAHAM AVENUE GRAND STREET . 1 0 0 0 0 0 1 0 Traffic Control Disregarded Unspecified . . . 4021780 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
66 11/15/2018 12:20 BRONX 10455 40.815361 -73.915955 (40.81536, -73.915955) . . 459 EAST 149 STREET 0 0 0 0 0 0 0 0 Passing Too Closely Passing Too Closely . . . 4021875 Sedan Tanker . . .
67 11/15/2018 12:20 BROOKLYN 11221 40.692356 -73.911064 (40.692356, -73.911064) HANCOCK STREET WILSON AVENUE . 0 0 0 0 0 0 0 0 Passing Too Closely Unspecified . . . 4021887 Station Wagon/Sport Utility Vehicle . . . .
68 11/15/2018 12:20 MANHATTAN 10022 40.755756 -73.96479 (40.755756, -73.96479) EAST 53 STREET 1 AVENUE . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Driver Inattention/Distraction . . . 4021680 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
69 11/15/2018 12:25 BRONX 10460 40.845863 -73.880013 (40.845863, -73.88001) . . 932 BRONX PARK SOUTH 0 0 0 0 0 0 0 0 Other Vehicular Reaction to Uninvolved Vehicle . . . 4021401 Bus Sedan . . .
70 11/15/2018 12:26 . . 40.689274 -73.99913 (40.689274, -73.99913) HICKS STREET CONGRESS STREET . 0 0 0 0 0 0 0 0 Turning Improperly Unspecified . . . 4021730 Sedan Sedan . . .
71 11/15/2018 12:28 . . 40.773335 -73.955063 (40.773335, -73.95506) 2 AVENUE . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021839 Station Wagon/Sport Utility Vehicle Sedan . . .
72 11/15/2018 12:30 . . 40.615692 -73.99366 (40.615692, -73.99366) 18 AVENUE . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021691 Sedan Station Wagon/Sport Utility Vehicle . . .
73 11/15/2018 12:30 . . 40.587967 -73.954346 (40.587967, -73.954346) AVENUE Z . . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021391 Station Wagon/Sport Utility Vehicle Sedan . . .
74 11/15/2018 14:07 STATEN ISLAND 10306 40.577946 -74.102325 (40.577946, -74.102325) HYLAN BOULEVARD MIDLAND AVENUE . 0 0 0 0 0 0 0 0 Backing Unsafely Unspecified . . . 4021765 Sedan Sedan . . .
75 11/15/2018 12:40 MANHATTAN 10128 40.777859 -73.945724 (40.77786, -73.945724) YORK AVENUE EAST 89 STREET . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021830 Box Truck Station Wagon/Sport Utility Vehicle . . .
76 11/15/2018 12:45 MANHATTAN 10016 40.745068 -73.97258 (40.745068, -73.97258) EAST 36 STREET 1 AVENUE . 0 0 0 0 0 0 0 0 Traffic Control Disregarded Unspecified . . . 4021470 Bike Station Wagon/Sport Utility Vehicle . . .
77 11/15/2018 12:57 . . 40.585686 -73.817238 (40.585686, -73.81724) BEACH 95 STREET . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021424 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
78 11/15/2018 13:00 BROOKLYN 11214 40.608593 -74.00486 (40.608593, -74.00486) 17 AVENUE 86 STREET . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021703 Bus Station Wagon/Sport Utility Vehicle . . .
79 11/15/2018 13:00 QUEENS 11373 40.745647 -73.88166 (40.745647, -73.88166) . . 40-49 ITHACA STREET 0 0 0 0 0 0 0 0 Traffic Control Disregarded Passing Too Closely . . . 4021396 Station Wagon/Sport Utility Vehicle Box Truck . . .
80 11/15/2018 13:00 . . 40.720566 -73.845901 (40.720566, -73.8459) 70 ROAD . . 0 0 0 0 0 0 0 0 Passing Too Closely Failure to Yield Right-of-Way . . . 4021435 Station Wagon/Sport Utility Vehicle Sedan . . .
81 11/15/2018 13:00 . . 40.607662 -73.983482 (40.607662, -73.98348) AVENUE P . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified Unspecified . . 4021694 Sedan Sedan Sedan . .
82 11/15/2018 13:00 . . 40.59565 -73.908165 (40.59565, -73.908165) BELT PARKWAY . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified Unspecified . . 4021579 Sedan Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . .
83 11/15/2018 13:00 . . 40.737709 -73.974266 (40.73771, -73.974266) FDR DRIVE . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021749 Station Wagon/Sport Utility Vehicle Sedan . . .
84 11/15/2018 13:05 MANHATTAN 10001 40.751862 -73.993721 (40.75186, -73.99372) . . 5 PENNSYLVANIA PLAZA 1 0 0 0 0 0 1 0 Brakes Defective Unspecified . . . 4021506 Sedan Taxi . . .
85 11/15/2018 13:06 . . . . . WILLIS AVE BRIDGE . . 0 0 0 0 0 0 0 0 Unspecified . . . . 4021361 Station Wagon/Sport Utility Vehicle . . . .
86 11/15/2018 13:20 BROOKLYN 11232 40.648346 -73.9991 (40.648346, -73.9991) . . 706 39 STREET 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021466 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
87 11/15/2018 13:20 . . 40.679863 -73.838837 (40.679863, -73.83884) 101 STREET ROCKAWAY BOULEVARD . 1 0 0 0 0 0 1 0 Following Too Closely Unspecified . . . 4021392 Station Wagon/Sport Utility Vehicle Sedan . . .
88 11/15/2018 13:25 . . 40.720482 -73.780106 (40.72048, -73.780106) GRAND CENTRAL PKWY . . 0 0 0 0 0 0 0 0 Unsafe Speed . . . . 4021380 Sedan . . . .
89 11/15/2018 13:25 QUEENS 11413 40.681843 -73.750099 (40.681843, -73.7501) 218 STREET 132 ROAD . 1 0 0 0 0 0 1 0 Unspecified Unspecified . . . 4021558 Sedan Sedan . . .
90 11/15/2018 13:30 BRONX 10457 40.84869 -73.90329 (40.84869, -73.90329) EAST TREMONT AVENUE ANTHONY AVENUE . 0 0 0 0 0 0 0 0 Passing Too Closely Unspecified . . . 4021744 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
91 11/15/2018 13:30 BROOKLYN 11203 40.645245 -73.932625 (40.645245, -73.932625) SCHENECTADY AVENUE CORTELYOU ROAD . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021367 Station Wagon/Sport Utility Vehicle Sedan . . .
92 11/15/2018 13:30 BROOKLYN 11214 40.601948 -73.99382 (40.601948, -73.99382) BAY PARKWAY 86 STREET . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021695 Station Wagon/Sport Utility Vehicle Sedan . . .
93 11/15/2018 13:30 . . 40.787174 -73.813492 (40.787174, -73.81349) CROSS ISLAND PARKWAY . . 1 0 0 0 0 0 1 0 Reaction to Uninvolved Vehicle Pavement Slippery . . . 4021602 Station Wagon/Sport Utility Vehicle Sedan . . .
94 11/15/2018 13:40 . . 40.735413 -73.91835 (40.735413, -73.91835) LAUREL HILL BOULEVARD 48 STREET . 0 0 0 0 0 0 0 0 Reaction to Uninvolved Vehicle Unspecified . . . 4021447 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
95 11/15/2018 13:42 . . 40.686077 -74.000381 (40.686077, -74.00038) BROOKLYN QUEENS EXPRESSWAY . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021731 Sedan Sedan . . .
96 11/15/2018 14:00 BROOKLYN 11226 40.637295 -73.959633 (40.637295, -73.95963) . . 1900 NEWKIRK AVENUE 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021505 Sedan Sedan . . .
97 11/15/2018 14:00 . . 40.714455 -73.963211 (40.714455, -73.96321) BERRY STREET . . 0 0 0 0 0 0 0 0 Failure to Yield Right-of-Way Unspecified . . . 4021786 Sedan Sedan . . .
98 11/15/2018 14:05 QUEENS 11357 40.780712 -73.825089 (40.780712, -73.82509) WHITESTONE EXPRESSWAY 21 AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021474 Sedan Sedan . . .
99 11/15/2018 14:15 QUEENS 11691 40.602295 -73.758629 (40.602295, -73.75863) CORNAGA AVENUE NASBY PLACE . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021800 Sedan Sedan . . .
100 11/15/2018 14:15 STATEN ISLAND 10312 40.559502 -74.166702 (40.5595, -74.1667) GURLEY AVENUE GETZ AVENUE . 0 0 0 0 0 0 0 0 Other Vehicular Following Too Closely . . . 4021766 Sedan Station Wagon/Sport Utility Vehicle . . .
101 11/15/2018 14:20 . . 40.603451 -74.009003 (40.60345, -74.009) CROPSEY AVENUE . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021687 Sedan Station Wagon/Sport Utility Vehicle . . .
102 11/15/2018 14:20 QUEENS 11434 40.660671 -73.768036 (40.66067, -73.768036) . . 146-80 BREWER BOULEVARD 2 0 0 0 0 0 2 0 Following Too Closely Unspecified . . . 4021521 Sedan Sedan . . .
103 11/15/2018 14:30 BROOKLYN 11215 40.668159 -73.991203 (40.66816, -73.9912) . . 204 13 STREET 0 0 0 0 0 0 0 0 Backing Unsafely Unspecified . . . 4021884 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
104 11/15/2018 14:30 QUEENS 11363 40.768398 -73.737549 (40.7684, -73.73755) NORTHERN BOULEVARD MARATHON PARKWAY . 0 0 0 0 0 0 0 0 Passing or Lane Usage Improper Unspecified . . . 4021439 Sedan Station Wagon/Sport Utility Vehicle . . .
105 11/15/2018 14:30 STATEN ISLAND 10306 40.580727 -74.107567 (40.580727, -74.10757) MIDLAND AVENUE NORTH RAILROAD AVENUE . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021779 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
106 11/15/2018 14:30 . . 40.575943 -74.11425 (40.575943, -74.11425) NORTH RAILROAD AVENUE . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021770 Sedan Station Wagon/Sport Utility Vehicle . . .
107 11/15/2018 14:35 . . 40.720955 -73.809349 (40.720955, -73.80935) 79 AVENUE . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021543 Sedan Sedan . . .
108 11/15/2018 14:35 . . 40.667313 -73.739258 (40.667313, -73.73926) LAURELTON PARKWAY . . 1 0 0 0 0 0 1 0 Pavement Slippery Unspecified Unspecified . . 4021451 Sedan Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . .
109 11/15/2018 14:37 QUEENS 11369 40.768131 -73.876251 (40.76813, -73.87625) 23 AVENUE 94 STREET . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021493 Sedan Sedan . . .
110 11/15/2018 14:40 BROOKLYN 11203 40.65794 -73.936874 (40.65794, -73.936874) WINTHROP STREET TROY AVENUE . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021654 Sedan Station Wagon/Sport Utility Vehicle . . .
111 11/15/2018 14:40 BROOKLYN 11229 40.597057 -73.941261 (40.597057, -73.94126) . . 3566 NOSTRAND AVENUE 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021709 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
112 11/15/2018 14:45 BROOKLYN 11210 40.621792 -73.947723 (40.62179, -73.94772) . . 2806 AVENUE L 0 0 0 0 0 0 0 0 Brakes Defective Unspecified . . . 4021509 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
113 11/15/2018 14:45 QUEENS 11374 40.72504 -73.868286 (40.72504, -73.868286) CARLTON STREET 63 AVENUE . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified Unspecified . . 4021626 Station Wagon/Sport Utility Vehicle Sedan Sedan . .
114 11/15/2018 14:45 STATEN ISLAND 10306 40.570747 -74.128098 (40.570747, -74.1281) TYSENS LANE DALTON AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021774 Sedan Station Wagon/Sport Utility Vehicle . . .
115 11/15/2018 14:45 . . 40.605888 -73.989723 (40.60589, -73.98972) 79 STREET . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021692 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
116 11/15/2018 14:45 . . . . . FLATBUSH AVENUE . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified Unspecified . . 4021885 Sedan Box Truck Sedan . .
117 11/15/2018 14:45 QUEENS 11420 40.666119 -73.82225 (40.66612, -73.82225) LEFFERTS BOULEVARD NORTH CONDUIT AVENUE . 1 0 0 0 0 0 1 0 Pavement Slippery Unspecified . . . 4021472 Sedan Station Wagon/Sport Utility Vehicle . . .
118 11/15/2018 14:50 QUEENS 11432 40.713791 -73.791878 (40.71379, -73.79188) KINGSTON PLACE HENLEY ROAD . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021584 Station Wagon/Sport Utility Vehicle Sedan . . .
119 11/15/2018 14:50 . . 40.757122 -73.971916 (40.757122, -73.97192) EAST 51 STREET . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021677 Sedan Bus . . .
120 11/15/2018 14:50 QUEENS 11354 40.764988 -73.820671 (40.764988, -73.82067) 146 STREET NORTHERN BOULEVARD . 1 0 1 0 0 0 0 0 Failure to Yield Right-of-Way . . . . 4021664 Sedan . . . .
121 11/15/2018 14:55 STATEN ISLAND 10306 40.577251 -74.102982 (40.57725, -74.10298) ZWICKY AVENUE HYLAN BOULEVARD . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021767 Station Wagon/Sport Utility Vehicle Sedan . . .
122 11/15/2018 15:20 BROOKLYN 11232 40.66457 -73.997131 (40.66457, -73.99713) 3 AVENUE 20 STREET . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021740 Motorcycle Box Truck . . .
123 11/15/2018 14:55 BROOKLYN 11206 40.702862 -73.937386 (40.70286, -73.937386) . . 421 BUSHWICK AVENUE 1 0 0 0 0 0 1 0 Pavement Slippery Unspecified . . . 4021773 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
124 11/15/2018 15:00 BRONX 10451 40.817001 -73.930016 (40.817, -73.930016) GERARD AVENUE EAST 144 STREET . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021872 Station Wagon/Sport Utility Vehicle Sedan . . .
125 11/15/2018 15:00 BROOKLYN 11214 40.601864 -74.002319 (40.601864, -74.00232) BAY 23 STREET BATH AVENUE . 0 0 0 0 0 0 0 0 Brakes Defective Unspecified . . . 4021702 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
126 11/15/2018 15:00 BROOKLYN 11226 40.649776 -73.949745 (40.649776, -73.949745) . . 185 ERASMUS STREET 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021515 Station Wagon/Sport Utility Vehicle Sedan . . .
127 11/15/2018 15:00 QUEENS 11418 40.702442 -73.816872 (40.702442, -73.81687) METROPOLITAN AVENUE JAMAICA AVENUE . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021758 Station Wagon/Sport Utility Vehicle . . . .
128 11/15/2018 15:00 QUEENS 11433 40.696159 -73.786613 (40.69616, -73.78661) 167 STREET 109 AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021913 Sedan Snow Plow . . .
129 11/15/2018 15:00 STATEN ISLAND 10304 40.607281 -74.092621 (40.60728, -74.09262) DOUGLAS COURT DOUGLAS ROAD . 0 0 0 0 0 0 0 0 Pavement Slippery . . . . 4021772 Pick-up Truck . . . .
130 11/15/2018 15:00 . . 40.612328 -73.9758 (40.612328, -73.9758) 24 AVENUE . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021696 Tractor Truck Diesel Station Wagon/Sport Utility Vehicle . . .
131 11/15/2018 15:00 . . 40.656864 -73.9981 (40.656864, -73.9981) 5 AVENUE . . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021741 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
132 11/15/2018 15:00 . . 40.599854 -73.988777 (40.599854, -73.98878) 85 STREET . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021688 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
133 11/15/2018 15:00 . . 40.715199 -73.836128 (40.7152, -73.83613) AUSTIN STREET . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021636 Sedan Station Wagon/Sport Utility Vehicle . . .
134 11/15/2018 15:00 . . . . . LITTLE NECK PARKWAY GRAND CENTRAL PARKWAY . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021566 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
135 11/15/2018 15:00 . . 40.653976 -73.952835 (40.653976, -73.952835) ROGERS AVENUE . . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021616 Box Truck Bus . . .
136 11/15/2018 15:00 BROOKLYN 11220 40.636299 -74.025375 (40.6363, -74.025375) . . 301 68 STREET 1 0 0 0 0 0 1 0 Following Too Closely Unspecified Unspecified . . 4021609 Station Wagon/Sport Utility Vehicle Sedan . . .
137 11/15/2018 15:05 . . 40.602295 -73.758629 (40.602295, -73.75863) CORNAGA AVENUE . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021803 Station Wagon/Sport Utility Vehicle Pick-up Truck . . .
138 11/15/2018 15:09 BRONX 10459 40.819691 -73.901611 (40.81969, -73.90161) WESTCHESTER AVENUE PROSPECT AVENUE . 0 0 0 0 0 0 0 0 Passing Too Closely Unspecified . . . 4021870 Sedan Sedan . . .
139 11/15/2018 15:10 BRONX 10458 40.860497 -73.894501 (40.860497, -73.8945) . . 333 EAST 188 STREET 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021742 Sedan Station Wagon/Sport Utility Vehicle . . .
140 11/15/2018 15:15 BROOKLYN 11229 40.601295 -73.954948 (40.601295, -73.95495) EAST 17 STREET AVENUE T . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021805 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
141 11/15/2018 15:15 QUEENS 11374 40.720554 -73.864258 (40.720554, -73.86426) 65 AVENUE FITCHETT STREET . 0 0 0 0 0 0 0 0 Passing Too Closely Unspecified . . . 4021634 Sedan Station Wagon/Sport Utility Vehicle . . .
142 11/15/2018 15:15 QUEENS 11414 40.664295 -73.839378 (40.664295, -73.83938) KILLARNEY STREET 156 AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021524 Sedan Sedan . . .
143 11/15/2018 15:15 . . . . . NEW ENGLAND THRUWAY . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021754 Pick-up Truck Sedan . . .
144 11/15/2018 15:18 BROOKLYN 11203 40.654152 -73.92511 (40.654152, -73.92511) . . 885 LINDEN BOULEVARD 1 0 0 0 0 0 1 0 Lost Consciousness Unspecified . . . 4021537 Sedan Sedan . . .
145 11/15/2018 15:20 BRONX 10456 40.82243 -73.909462 (40.82243, -73.90946) 3 AVENUE SAINT ANNS AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021867 Station Wagon/Sport Utility Vehicle Taxi . . .
146 11/15/2018 15:20 BROOKLYN 11206 40.699997 -73.95723 (40.699997, -73.95723) BEDFORD AVENUE LYNCH STREET . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021787 Taxi Sedan . . .
147 11/15/2018 15:20 . . . . . 168 STREET GRAND CENTRAL PARKWAY . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021578 Sedan Station Wagon/Sport Utility Vehicle . . .
148 11/15/2018 15:20 . . 40.687832 -73.930038 (40.68783, -73.93004) REID AVENUE . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021819 Sedan Sedan . . .
149 11/15/2018 15:20 . . 40.762737 -73.839508 (40.762737, -73.83951) VAN WYCK EXPWY . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021716 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
150 11/15/2018 15:21 BROOKLYN 11214 40.615707 -74.001457 (40.615707, -74.00146) 76 STREET 16 AVENUE . 2 0 0 0 0 0 2 0 Failure to Yield Right-of-Way Pavement Slippery . . . 4021706 Sedan Sedan . . .
151 11/15/2018 15:24 BROOKLYN 11204 40.611446 -73.99115 (40.611446, -73.99115) 74 STREET 20 AVENUE . 0 0 0 0 0 0 0 0 Failure to Yield Right-of-Way Unspecified . . . 4021698 Station Wagon/Sport Utility Vehicle . . . .
152 11/15/2018 15:25 STATEN ISLAND 10306 40.554855 -74.13353 (40.554855, -74.13353) DURANT AVENUE JUSTIN AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021769 Station Wagon/Sport Utility Vehicle Sedan . . .
153 11/15/2018 15:25 . . 40.730679 -73.77581 (40.73068, -73.77581) UNION TURNPIKE . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021531 Station Wagon/Sport Utility Vehicle Sedan . . .
154 11/15/2018 15:25 . . 40.79525 -73.973213 (40.79525, -73.97321) WEST 96 STREET . . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021856 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
155 11/15/2018 15:25 . . 40.644562 -73.988358 (40.64456, -73.98836) . . FORT HAMILTON PARKWAY 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021810 Sedan . . . .
156 11/15/2018 15:28 BROOKLYN 11238 40.675362 -73.969696 (40.67536, -73.969696) PLAZA STREET EAST VANDERBILT AVENUE . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021911 Station Wagon/Sport Utility Vehicle Sedan . . .
157 11/15/2018 15:30 BROOKLYN 11221 40.695282 -73.925758 (40.695282, -73.92576) STANHOPE STREET EVERGREEN AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021846 Box Truck Station Wagon/Sport Utility Vehicle . . .
158 11/15/2018 15:30 MANHATTAN 10033 40.851322 -73.940292 (40.851322, -73.94029) WEST 181 STREET CABRINI BOULEVARD . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021807 Station Wagon/Sport Utility Vehicle Box Truck . . .
159 11/15/2018 15:30 QUEENS 11364 40.76038 -73.758011 (40.76038, -73.75801) 46 AVENUE CLOVERDALE BOULEVARD . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021591 Station Wagon/Sport Utility Vehicle Sedan . . .
160 11/15/2018 15:30 QUEENS 11417 40.676617 -73.857903 (40.676617, -73.8579) 79 STREET PITKIN AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021522 Station Wagon/Sport Utility Vehicle Pick-up Truck . . .
161 11/15/2018 15:30 QUEENS 11435 40.695087 -73.802917 (40.695087, -73.80292) SUTPHIN BOULEVARD 106 AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021914 Station Wagon/Sport Utility Vehicle Sedan . . .
162 11/15/2018 15:30 . . 40.72176 -73.76001 (40.72176, -73.76001) FRANCIS LEWIS BOULEVARD . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021573 Station Wagon/Sport Utility Vehicle Sedan . . .
163 11/15/2018 15:30 . . 40.746441 -73.879822 (40.74644, -73.87982) GLEANE STREET . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified . . . 4021710 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
164 11/15/2018 15:30 . . 40.708004 -73.844582 (40.708004, -73.84458) JACKIE ROBINSON PKWY . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021747 Sedan Sedan . . .
165 11/15/2018 15:30 QUEENS 11354 40.763161 -73.816399 (40.76316, -73.8164) 149 STREET ROOSEVELT AVENUE . 1 0 1 0 0 0 0 0 Failure to Yield Right-of-Way . . . . 4021606 Station Wagon/Sport Utility Vehicle . . . .
166 11/15/2018 15:30 QUEENS 11434 40.671566 -73.78466 (40.671566, -73.78466) 132 AVENUE ROCKAWAY BOULEVARD . 1 0 0 0 0 0 1 0 Driver Inattention/Distraction Driver Inattention/Distraction . . . 4021674 Sedan Station Wagon/Sport Utility Vehicle . . .
167 11/15/2018 15:30 . . 40.663906 -73.950981 (40.663906, -73.95098) EMPIRE BOULEVARD . . 1 0 1 0 0 0 0 0 Pavement Slippery . . . . 4021671 Station Wagon/Sport Utility Vehicle . . . .
168 11/15/2018 15:30 . . 40.89975 -73.891594 (40.89975, -73.891594) HENRY HUDSON PARKWAY . . 1 0 0 0 0 0 1 0 Pavement Slippery Unspecified . . . 4021750 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
169 11/15/2018 15:30 . . . . . LITTLE NECK PARKWAY GRAND CENTRAL PARKWAY . 1 0 0 0 0 0 1 0 Pavement Slippery Unspecified . . . 4021556 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
170 11/15/2018 15:33 STATEN ISLAND 10306 40.571251 -74.119904 (40.57125, -74.1199) SOUTH RAILROAD AVENUE BEACH AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021771 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
171 11/15/2018 15:33 BRONX 10454 40.807808 -73.923843 (40.807808, -73.92384) WILLIS AVENUE EAST 136 STREET . 1 0 0 0 0 0 1 0 Other Vehicular Failure to Yield Right-of-Way . . . 4021871 Station Wagon/Sport Utility Vehicle Sedan . . .
172 11/15/2018 15:35 BRONX 10455 40.820133 -73.913071 (40.820133, -73.91307) EAST 156 STREET 3 AVENUE . 0 0 0 0 0 0 0 0 Unspecified Unspecified Unspecified . . 4021869 Sedan Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . .
173 11/15/2018 15:35 QUEENS 11368 40.739056 -73.862869 (40.739056, -73.86287) 98 STREET 55 AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021717 Station Wagon/Sport Utility Vehicle Station Wagon/Sport Utility Vehicle . . .
174 11/15/2018 15:35 . . 40.668495 -73.925606 (40.668495, -73.925606) EASTERN PARKWAY . . 0 0 0 0 0 0 0 0 Driver Inattention/Distraction Unspecified . . . 4021661 Sedan Sedan . . .
175 11/15/2018 15:35 . . 40.806309 -73.932961 (40.80631, -73.93296) HARLEM RIVER DRIVE . . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified Unspecified . . 4021752 Station Wagon/Sport Utility Vehicle Sedan Station Wagon/Sport Utility Vehicle . .
176 11/15/2018 15:35 QUEENS 11355 40.752468 -73.83197 (40.75247, -73.83197) . . 132-26 AVERY AVENUE 1 0 0 0 0 0 1 0 Pavement Slippery Pavement Slippery . . . 4021618 Taxi Station Wagon/Sport Utility Vehicle . . .
177 11/15/2018 15:38 BROOKLYN 11206 40.701427 -73.945381 (40.701427, -73.94538) . . 95 THROOP AVENUE 0 0 0 0 0 0 0 0 Backing Unsafely Unspecified . . . 4021784 Station Wagon/Sport Utility Vehicle Pick-up Truck . . .
178 11/15/2018 15:38 BROOKLYN 11215 40.667084 -73.987236 (40.667084, -73.987236) . . 292 12 STREET 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021895 Station Wagon/Sport Utility Vehicle Box Truck . . .
179 11/15/2018 15:38 MANHATTAN 10031 40.822109 -73.94809 (40.82211, -73.94809) CONVENT AVENUE WEST 141 STREET . 2 0 0 0 0 0 2 0 Pavement Slippery Pavement Slippery Pavement Slippery Pavement Slippery . 4021557 Taxi Sedan Taxi Sedan .
180 11/15/2018 15:40 MANHATTAN 10016 40.750904 -73.980682 (40.750904, -73.98068) MADISON AVENUE EAST 39 STREET . 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021915 Box Truck Sedan . . .
181 11/15/2018 15:40 QUEENS 11354 . . . PRINCE STREET NORTHERN BOULEVARD . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021665 Sedan Convertible . . .
182 11/15/2018 15:40 . . 40.739162 -73.785561 (40.739162, -73.78556) 64 AVENUE . . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021547 Sedan Sedan . . .
183 11/15/2018 15:45 . . 40.633904 -73.993294 (40.633904, -73.99329) . . 51 STREET 0 0 0 0 0 0 0 0 Unspecified Unspecified . . . 4021809 Sedan . . . .
184 11/15/2018 15:45 . . 40.807594 -73.966988 (40.807594, -73.96699) RIVERSIDE DRIVE . . 1 0 0 0 0 0 1 0 Steering Failure Unspecified . . . 4021548 Taxi Station Wagon/Sport Utility Vehicle . . .
185 11/15/2018 15:48 . . 40.599072 -73.904984 (40.59907, -73.90498) BELT PARKWAY . . 1 0 0 0 0 0 1 0 Driver Inattention/Distraction Unspecified . . . 4021585 Sedan Station Wagon/Sport Utility Vehicle . . .
186 11/15/2018 15:49 QUEENS 11104 40.741585 -73.922813 (40.741585, -73.92281) 42 STREET 47 AVENUE . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021561 Sedan Station Wagon/Sport Utility Vehicle . . .
187 11/15/2018 15:49 QUEENS 11355 40.761471 -73.818085 (40.76147, -73.818085) 147 STREET 41 AVENUE . 1 0 0 0 0 0 1 0 Failure to Yield Right-of-Way Unspecified . . . 4021658 Station Wagon/Sport Utility Vehicle Sedan . . .
188 11/15/2018 15:50 BROOKLYN 11203 40.661217 -73.931412 (40.661217, -73.93141) . . 506 UTICA AVENUE 0 0 0 0 0 0 0 0 Pedestrian/Bicyclist/Other Pedestrian Error/Co... Driver Inexperience . . . 4021648 Bike Sedan . . .
189 11/15/2018 15:50 BROOKLYN 11206 40.709442 -73.936569 (40.709442, -73.93657) . . 289 SCHOLES STREET 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021781 Sedan Station Wagon/Sport Utility Vehicle . . .
190 11/15/2018 15:50 QUEENS 11422 40.660946 -73.738876 (40.660946, -73.73888) CANEY ROAD 243 STREET . 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021553 Sedan Station Wagon/Sport Utility Vehicle . . .
191 11/15/2018 15:52 QUEENS 11375 40.70969 -73.846031 (40.70969, -73.84603) METROPOLITAN AVENUE ASCAN AVENUE . 1 0 0 0 0 0 1 0 Pavement Slippery Unspecified . . . 4021628 Station Wagon/Sport Utility Vehicle Sedan . . .
192 11/15/2018 15:53 BROOKLYN 11223 40.600376 -73.963242 (40.600376, -73.96324) . . 812 AVENUE T 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021711 Station Wagon/Sport Utility Vehicle Sedan . . .
193 11/15/2018 15:59 . . 40.689831 -73.97863 (40.68983, -73.97863) ASHLAND PLACE . . 0 0 0 0 0 0 0 0 Unsafe Speed Unspecified . . . 4021518 Motorcycle Sedan . . .
194 11/15/2018 15:59 . . . . . KISSENA BOULEVARD HORACE HARDING EXPRESSWAY . 0 0 0 0 0 0 0 0 Following Too Closely Unspecified Unspecified . . 4021575 Station Wagon/Sport Utility Vehicle Sedan Sedan . .
195 11/15/2018 16:00 BRONX 10455 40.816372 -73.903343 (40.816372, -73.90334) . . 844 EAST 155 STREET 0 0 0 0 0 0 0 0 Unspecified . . . . 4021868 Sedan . . . .
196 11/15/2018 16:00 BROOKLYN 11206 40.703762 -73.935623 (40.703762, -73.93562) . . 210 VARET STREET 0 0 0 0 0 0 0 0 Pavement Slippery Unspecified . . . 4021788 Sedan Station Wagon/Sport Utility Vehicle . . .
197 11/15/2018 16:00 BROOKLYN 11212 40.667366 -73.922737 (40.667366, -73.92274) RALPH AVENUE UNION STREET . 0 0 0 0 0 0 0 0 Unspecified . . . . 4021746 Sedan . . . .
198 11/15/2018 16:00 BROOKLYN 11225 40.658222 -73.950363 (40.658222, -73.95036) . . 1193 NOSTRAND AVENUE 0 0 0 0 0 0 0 0 Passing or Lane Usage Improper Pavement Slippery . . . 4021656 Box Truck Sedan . . .
199 11/15/2018 16:00 BROOKLYN 11228 40.618244 -74.002403 (40.618244, -74.0024) 15 AVENUE 74 STREET . 0 0 0 0 0 0 0 0 Unspecified . . . . 4021705 Sedan . . . .
200 11/15/2018 16:00 BROOKLYN 11235 40.586102 -73.960281 (40.5861, -73.96028) . . 2887 CONEY ISLAND AVENUE 0 0 0 0 0 0 0 0 Pavement Slippery . . . . 4021715 Station Wagon/Sport Utility Vehicle . . . .

Re-order variables

Personally, I prefer ID type variables to be in the first few columns of the dataset. We will use Stata’s order command to move the variable uniquekey to the first column.

[5]:
order(uniquekey), before(date)

Create date time variables

  1. Preview the date variable (through list, codebook, browse(Data Editor))
  2. Determine the format of the date variable
    • Is it a string, int, float, etc
  3. Determine if it is in other types of date format (e.g. Excel’s date format)
[6]:
list date in 1/4

     +------------+
     |       date |
     |------------|
  1. | 11/16/2018 |
  2. | 11/16/2018 |
  3. | 11/16/2018 |
  4. | 11/16/2018 |
     +------------+
[7]:
codebook date

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
date                                                                                                                                                                                                                                                       DATE
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                  type:  string (str105)

         unique values:  2,331                    missing "":  0/1,386,003

              examples:  "03/24/2015"
                         "06/08/2017"
                         "08/15/2015"
                         "10/20/2013"

               warning:  variable has leading and embedded blanks

Now, we determine that the date variable is in a string format. We will use Stata’s HRF-to-SIF conversion functions in the datetime command.

[8]:
gen date_SIF = date(date, "MDY"),after(date)
(1 missing value generated)

We know that the date is a string variable, and it is in HRF( Human readable form) in the form of Month/Day/Year. We tell Stata’s date() function that through the mask "MDY". Then, I tell Stata to put date_SIF in the column after date.

[9]:
list date_SIF in 1/4

     +----------+
     | date_SIF |
     |----------|
  1. |    21504 |
  2. |    21504 |
  3. |    21504 |
  4. |    21504 |
     +----------+

The variable is in SIF(Stata internal form) now. So Stata understands it is a date. But in order for us to understand, we tell Stata to display this number in HRF through the format command. The date format is %td and the time format is %tc.

[10]:
format date_SIF %td

We repeat this for time variable.

[11]:
gen time_SIF = clock(time, "hm"),after(time)
format time_SIF %tc

(1 missing value generated)

Why do we want Stata to understand dates and times?

Because it allows us to do so much more! - view summary statistics of the date (e.g. first date, last date) - create year and month variables - create dummies based on durations - create dummies based on boolean logic

[12]:
sum date_SIF, format

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
    date_SIF |  1,386,002   14oct2015    669.6435  01jul2012  16nov2018
[13]:
gen year_SIF = year(date_SIF) // Extract year from the date
(1 missing value generated)
[14]:
gen month_SIF = month(date_SIF) // Extract month from the date
(1 missing value generated)
[15]:
gen special_period = (date_SIF >= td(14feb2014) & date_SIF <= td(14march2014))
tab special_period



special_per |
        iod |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |  1,370,768       98.90       98.90
          1 |     15,235        1.10      100.00
------------+-----------------------------------
      Total |  1,386,003      100.00

Renaming long variable names

This is entirely up to you. I prefer a shorter variable name, and use labels to store the longer and more descriptive name. Labeling is the more important thing to do when you begin this process of building an analytical dataset.

[16]:
rename numberofcyclistinjured cyclistinjured
rename numberofcyclistkilled cyclistkilled
[17]:
label variable cyclistinjured "Number of cyclists injured"
label variable cyclistkilled "Number of cyclists killed"

Creating dummies from long strings

I will show you several methods to finding keywords from string variables. For example, we want to create a variable to indicate if a collision occurred with a bicycle. - strpos - contains exact “string”

  1. Explore the string variable that we want to extract information from.
[18]:
tab vehicletypecode1

                VEHICLE TYPE CODE 1 |      Freq.     Percent        Cum.
------------------------------------+-----------------------------------
                              (ceme |          1        0.00        0.00
                              15 Pa |          1        0.00        0.00
                              2 TON |          1        0.00        0.00
                         2 dr sedan |          6        0.00        0.00
                             3-Door |         30        0.00        0.00
                              315 e |          1        0.00        0.00
                                 3D |         32        0.00        0.01
                         4 dr sedan |        202        0.01        0.02
                               4DSD |          3        0.00        0.02
                              4whee |          1        0.00        0.02
                              99999 |          1        0.00        0.02
                              ABULA |          1        0.00        0.02
                              ACCES |          2        0.00        0.02
                                 AM |        292        0.02        0.04
                              AMABU |          1        0.00        0.04
                                AMB |          7        0.00        0.04
                               AMBU |          5        0.00        0.04
                              AMBUL |        111        0.01        0.05
                          AMBULANCE |      2,406        0.17        0.23
                              AMbul |          1        0.00        0.23
                              APORT |          1        0.00        0.23
                              APPOR |          1        0.00        0.23
                                 AR |         41        0.00        0.23
                              ARMOR |          1        0.00        0.23
                               ARMY |          1        0.00        0.23
                              Ambul |         20        0.00        0.23
                          Ambulance |        265        0.02        0.25
                      Armored Truck |         42        0.00        0.25
                                 BA |          6        0.00        0.25
                               BACK |          2        0.00        0.25
                              BACKH |          4        0.00        0.25
                              BED T |          1        0.00        0.25
                              BICYC |          1        0.00        0.25
                            BICYCLE |      5,567        0.40        0.66
                               BOAT |          1        0.00        0.66
                              BOBCA |          3        0.00        0.66
                                BOX |          1        0.00        0.66
                              BOX T |          5        0.00        0.66
                                 BR |         42        0.00        0.66
                                 BS |          2        0.00        0.66
                                 BU |      2,229        0.16        0.82
                              BULLD |          2        0.00        0.82
                                BUS |     14,057        1.02        1.85
                                BUs |          1        0.00        1.85
                              Backh |          1        0.00        1.85
                     Beverage Truck |         31        0.00        1.85
                               Bike |      1,912        0.14        1.99
                               Boom |          1        0.00        1.99
                                Box |          1        0.00        1.99
                          Box Truck |      2,676        0.19        2.18
                              Box t |          1        0.00        2.18
                              Bucke |          1        0.00        2.18
                   Bulk Agriculture |          4        0.00        2.18
                                Bus |      1,992        0.14        2.33
                              CABIN |          1        0.00        2.33
                               CAMP |          1        0.00        2.33
                              CARGO |          2        0.00        2.33
                               CART |          1        0.00        2.33
                                 CB |         89        0.01        2.34
                              CHERR |          1        0.00        2.34
                              CHEVY |          1        0.00        2.34
                                 CM |         41        0.00        2.34
                               CMIX |          1        0.00        2.34
                                 CO |          2        0.00        2.34
                                COM |          8        0.00        2.34
                              COM T |          1        0.00        2.34
                               COM. |          1        0.00        2.34
                               COMB |          1        0.00        2.34
                              COMME |         11        0.00        2.34
                              COMMM |          1        0.00        2.34
                              CONCR |          1        0.00        2.34
                              CONST |          1        0.00        2.34
                               CONV |        398        0.03        2.37
                              COUPE |          1        0.00        2.37
                              CRANE |          1        0.00        2.37
                              CUSHM |          1        0.00        2.37
                              Cargo |          1        0.00        2.37
                          Carry All |        178        0.01        2.38
                              Cat 9 |          1        0.00        2.38
                        Chassis Cab |         83        0.01        2.39
                               Cmix |          1        0.00        2.39
                              Comix |          1        0.00        2.39
                               Comm |          1        0.00        2.39
                              Comme |          1        0.00        2.39
                     Concrete Mixer |         67        0.00        2.39
                        Convertible |        428        0.03        2.42
                              DELIV |         14        0.00        2.43
                               DELV |          2        0.00        2.43
                              DEMA- |          1        0.00        2.43
                               DIRT |          1        0.00        2.43
                              DOT R |          1        0.00        2.43
                                 DP |        367        0.03        2.45
                                 DS |      1,006        0.07        2.53
                               DUMP |         17        0.00        2.53
                              DUMPT |          1        0.00        2.53
                              DUNBA |          1        0.00        2.53
                              Deliv |          1        0.00        2.53
                               Dump |        414        0.03        2.56
                              Dumps |          1        0.00        2.56
                              E BIK |          1        0.00        2.56
                              E ONE |          1        0.00        2.56
                              E SCO |          1        0.00        2.56
                              E-BIK |          4        0.00        2.56
                              E-Bik |          2        0.00        2.56
                              E-MOT |          1        0.00        2.56
                              E/BIK |          1        0.00        2.56
                              EBIKE |          2        0.00        2.56
                              ELECT |         15        0.00        2.56
                              EMRGN |          1        0.00        2.56
                              EMS A |          1        0.00        2.56
                              EMS H |          1        0.00        2.56
                                 EN |          2        0.00        2.56
                                EPO |          1        0.00        2.56
                              Elect |          3        0.00        2.56
Enclosed Body - Removable Enclosure |          2        0.00        2.56
                                 FB |        238        0.02        2.58
                              FD NY |          1        0.00        2.58
                              FD tr |          1        0.00        2.58
                               FDNY |         34        0.00        2.58
                              FED E |          2        0.00        2.58
                               FIRE |         25        0.00        2.58
                         FIRE TRUCK |        850        0.06        2.64
                              FIRET |         10        0.00        2.64
                               FLAT |          9        0.00        2.65
                              FLATB |          1        0.00        2.65
                               FORD |          4        0.00        2.65
                               FORK |          2        0.00        2.65
                              FORK- |          1        0.00        2.65
                              FORKL |         11        0.00        2.65
                                 FR |         34        0.00        2.65
                               FREE |          1        0.00        2.65
                              FREIG |          6        0.00        2.65
                              FRONT |          1        0.00        2.65
                              Fd fi |          1        0.00        2.65
                               Fdny |          2        0.00        2.65
                               Fire |         15        0.00        2.65
                              Firet |          1        0.00        2.65
                           Flat Bed |        226        0.02        2.67
                          Flat Rack |         51        0.00        2.67
                               Ford |          1        0.00        2.67
                              Forkl |          2        0.00        2.67
                              Freig |          1        0.00        2.67
                              GARBA |          4        0.00        2.67
                              GE/SC |          1        0.00        2.67
                                 GG |        205        0.01        2.69
                               GOLF |          1        0.00        2.69
                              GOVER |          1        0.00        2.69
                                 GR |          2        0.00        2.69
                               GRAY |          1        0.00        2.69
                              Garba |          2        0.00        2.69
                  Garbage or Refuse |        223        0.02        2.70
                         Glass Rack |          1        0.00        2.70
                               Golf |          2        0.00        2.70
                               H/WH |          1        0.00        2.70
                              HEAVY |          1        0.00        2.70
                               HELP |          1        0.00        2.70
                                 HO |          1        0.00        2.70
                              HORSE |          2        0.00        2.70
                              HWY C |          1        0.00        2.70
                               Hand |          1        0.00        2.70
                             Hopper |          3        0.00        2.70
                              Humme |          1        0.00        2.70
                                 IP |          1        0.00        2.70
                              Ice C |          1        0.00        2.71
                              Inter |          1        0.00        2.71
                               John |          1        0.00        2.71
     LARGE COM VEH(6 OR MORE TIRES) |     14,527        1.06        3.76
                                 LF |         16        0.00        3.76
                              LIBER |          1        0.00        3.76
                               LIMO |          3        0.00        3.76
                              LIVER |          2        0.00        3.76
                     LIVERY VEHICLE |     10,481        0.76        4.53
                                 LL |        169        0.01        4.54
                              LOADE |          1        0.00        4.54
                                 LP |          1        0.00        4.54
                                 LW |          2        0.00        4.54
                               Lift |          1        0.00        4.54
                          Lift Boom |         19        0.00        4.54
                              Light |          1        0.00        4.54
                              Limou |          1        0.00        4.54
                     Livestock Rack |          2        0.00        4.54
                                Log |          1        0.00        4.54
                        Lunch Wagon |          4        0.00        4.54
                               MACK |          2        0.00        4.54
                               MAIL |          4        0.00        4.54
                               MARK |          1        0.00        4.54
                                 MB |         20        0.00        4.54
                                 MD |         32        0.00        4.54
                                 MH |          3        0.00        4.54
                              MILLI |          1        0.00        4.54
                               MINI |          3        0.00        4.54
                                 MK |          1        0.00        4.54
                              MOPED |         10        0.00        4.55
                              MOTOR |          6        0.00        4.55
                         MOTORCYCLE |      6,536        0.48        5.02
                                 MS |         42        0.00        5.02
                              MTA B |          4        0.00        5.02
                              Marke |          1        0.00        5.02
                               Mini |          1        0.00        5.02
                           Minibike |          7        0.00        5.03
                          Minicycle |          4        0.00        5.03
                              Mo pa |          1        0.00        5.03
                              Moped |         56        0.00        5.03
                              Motor |          2        0.00        5.03
                          Motorbike |         41        0.00        5.03
                         Motorcycle |        877        0.06        5.10
                     Motorized Home |          4        0.00        5.10
                       Motorscooter |         62        0.00        5.10
              Multi-Wheeled Vehicle |         15        0.00        5.10
                                N/A |          1        0.00        5.10
                              NEW Y |          2        0.00        5.10
                              NS AM |          1        0.00        5.10
                              NYC B |          1        0.00        5.10
                              NYC M |          1        0.00        5.10
                              NYC a |          1        0.00        5.10
                               NYPD |          2        0.00        5.10
                              OIL T |          2        0.00        5.10
                                OML |          1        0.00        5.10
                              OMNIB |          1        0.00        5.10
                                OMR |          5        0.00        5.10
                                OMT |          2        0.00        5.10
                              OTHER |     23,972        1.74        6.85
                          Open Body |          5        0.00        6.85
                               P/SE |          1        0.00        6.85
                               P/SH |          2        0.00        6.85
                                PAS |          2        0.00        6.85
                  PASSENGER VEHICLE |    715,227       52.00       58.85
                              PEDIC |          1        0.00       58.85
                            PEDICAB |         35        0.00       58.85
                              PICK- |          1        0.00       58.85
                      PICK-UP TRUCK |     23,069        1.68       60.53
                              PICKU |          4        0.00       60.53
                                 PK |        405        0.03       60.56
                                 PL |          6        0.00       60.56
                                 PM |          7        0.00       60.56
                              POSTA |          4        0.00       60.56
                              POSTO |          1        0.00       60.56
                              POWER |          7        0.00       60.56
                                PSD |          4        0.00       60.56
                                PSR |          1        0.00       60.56
                               PUMP |          1        0.00       60.56
                             Pallet |          7        0.00       60.56
                            Pedicab |         11        0.00       60.56
                               Pick |          1        0.00       60.56
                              Pick- |          1        0.00       60.56
                      Pick-up Truck |      3,883        0.28       60.85
                              Picku |          1        0.00       60.85
         Pickup with mounted Camper |          5        0.00       60.85
                              Porta |          1        0.00       60.85
                                R/V |          1        0.00       60.85
                              R/V C |          1        0.00       60.85
                              REFRI |          3        0.00       60.85
                              RENTA |          1        0.00       60.85
                                REP |          2        0.00       60.85
                              RESCU |          1        0.00       60.85
                                 RF |         56        0.00       60.85
                                 RV |         11        0.00       60.85
                              RYDER |          1        0.00       60.85
                   Refrigerated Van |         58        0.00       60.86
                              SANIT |          6        0.00       60.86
                              SCHOO |         16        0.00       60.86
                              SCOOT |         10        0.00       60.86
                            SCOOTER |        256        0.02       60.88
                                 SE |          1        0.00       60.88
                               SELF |          4        0.00       60.88
                               SEMI |          2        0.00       60.88
                              SEMI- |          1        0.00       60.88
                              SKATE |          3        0.00       60.88
            SMALL COM VEH(4 TIRES)  |     14,559        1.06       61.94
                              SMART |          1        0.00       61.94
                               SNOW |          1        0.00       61.94
                                 SP |         20        0.00       61.94
                                SPC |          2        0.00       61.94
      SPORT UTILITY / STATION WAGON |    313,498       22.79       84.73
                              SPRIN |          1        0.00       84.73
                                 ST |         15        0.00       84.74
                              ST150 |          1        0.00       84.74
                               STAK |          5        0.00       84.74
                              STREE |          6        0.00       84.74
                              SUBN/ |          1        0.00       84.74
                              SUBUR |          1        0.00       84.74
                                SUV |          3        0.00       84.74
                              SWEEP |          1        0.00       84.74
                              Sanit |          1        0.00       84.74
                              Schoo |          1        0.00       84.74
                         School Bus |          9        0.00       84.74
                              Scoot |          1        0.00       84.74
                              Sedan |     56,607        4.12       88.85
                               Semi |          1        0.00       88.85
                              Semi- |          1        0.00       88.85
                              Skate |          1        0.00       88.85
                              Smart |          1        0.00       88.85
                          Snow Plow |          1        0.00       88.85
                                Spc |          1        0.00       88.85
                              Sprin |          2        0.00       88.85
                      Stake or Rack |         11        0.00       88.85
Station Wagon/Sport Utility Vehicle |     43,706        3.18       92.03
                               Subn |          1        0.00       92.03
                              Sweep |          1        0.00       92.03
                               TANK |          2        0.00       92.03
                              TANKE |          2        0.00       92.03
                               TAXI |     50,669        3.68       95.72
                                 TK |      2,485        0.18       95.90
                                 TN |         77        0.01       95.90
                                TOW |          3        0.00       95.90
                              TOW T |         13        0.00       95.90
                              TOWTR |          2        0.00       95.90
                                 TR |        160        0.01       95.92
                               TRAC |          3        0.00       95.92
                              TRACK |          1        0.00       95.92
                              TRACT |         21        0.00       95.92
                              TRAFF |          1        0.00       95.92
                              TRAIL |         52        0.00       95.92
                              TRANS |          3        0.00       95.92
                                TRK |          2        0.00       95.92
                                TRL |          3        0.00       95.92
                              TRLPM |          1        0.00       95.92
                               TRLR |          2        0.00       95.92
                              TRUCK |         45        0.00       95.93
                                 TT |        130        0.01       95.94
                             Tanker |         76        0.01       95.94
                               Taxi |      5,894        0.43       96.37
                                Tow |          1        0.00       96.37
                              Tow T |          1        0.00       96.37
                          Tow Truck |          4        0.00       96.37
                Tow Truck / Wrecker |        125        0.01       96.38
                              Tow t |          1        0.00       96.38
                              Tow-t |          1        0.00       96.38
                              Tract |          3        0.00       96.38
               Tractor Truck Diesel |      1,175        0.09       96.46
             Tractor Truck Gasoline |        159        0.01       96.48
                              Trail |          5        0.00       96.48
                              Truck |          2        0.00       96.48
                               U.S. |          1        0.00       96.48
                              UHAUL |          4        0.00       96.48
                              UHUAL |          1        0.00       96.48
                                UKN |          1        0.00       96.48
                              UNKNO |          1        0.00       96.48
                            UNKNOWN |     19,929        1.45       97.93
                              UPS T |          1        0.00       97.93
                              UPS t |          1        0.00       97.93
                              US PO |          1        0.00       97.93
                               USPS |         16        0.00       97.93
                                 UT |          1        0.00       97.93
                               UTIL |          4        0.00       97.93
                              UTILI |          6        0.00       97.93
                                UTV |          1        0.00       97.93
                              Unkno |          1        0.00       97.93
                               Util |          1        0.00       97.93
                              Utili |          2        0.00       97.93
                                VAN |     26,540        1.93       99.86
                              VAN T |          1        0.00       99.86
                              VAN/T |          2        0.00       99.86
                               VANG |          1        0.00       99.86
                                 VC |          2        0.00       99.86
                                 VN |        712        0.05       99.91
                                 VT |          2        0.00       99.91
                                Van |        906        0.07       99.98
                         Van Camper |          4        0.00       99.98
                            Vanette |          1        0.00       99.98
                              Veriz |          1        0.00       99.98
                              WAGON |          1        0.00       99.98
                                 WD |          1        0.00       99.98
                               WHBL |          1        0.00       99.98
                              WHEEL |          1        0.00       99.98
                              WHITE |          1        0.00       99.98
                               WORK |          2        0.00       99.98
                              Wagon |          1        0.00       99.98
                       Well Driller |          1        0.00       99.98
                              Wheel |          1        0.00       99.98
                               Work |          1        0.00       99.98
                              YELLO |          2        0.00       99.98
                               ambu |          5        0.00       99.98
                              ambul |         21        0.00       99.98
                              armor |          1        0.00       99.98
                              box t |          7        0.00       99.98
                              bulld |          1        0.00       99.98
                                bus |          7        0.00       99.98
                              cargo |          1        0.00       99.98
                               cate |          1        0.00       99.98
                              cemen |          1        0.00       99.98
                              chevo |          1        0.00       99.98
                                com |          2        0.00       99.98
                              comme |          3        0.00       99.98
                              deliv |          3        0.00       99.98
                               delv |          3        0.00       99.98
                                 dp |          1        0.00       99.98
                               dsny |          1        0.00       99.98
                               dump |          5        0.00       99.98
                              e BIK |          1        0.00       99.98
                              e amb |          1        0.00       99.98
                              e com |          1        0.00       99.98
                              e sco |          1        0.00       99.98
                              e-bik |          2        0.00       99.98
                               east |          1        0.00       99.98
                              elect |          3        0.00       99.98
                               f550 |          1        0.00       99.98
                              fd tr |          1        0.00       99.98
                               fdny |          8        0.00       99.98
                               fire |         13        0.00       99.99
                              firet |          1        0.00       99.99
                               flat |          1        0.00       99.99
                              flatb |          1        0.00       99.99
                               ford |          2        0.00       99.99
                               fork |          1        0.00       99.99
                              forkl |          4        0.00       99.99
                              garba |          3        0.00       99.99
                              gator |          1        0.00       99.99
                              ice c |          1        0.00       99.99
                              icecr |          1        0.00       99.99
                              ladde |          1        0.00       99.99
                              light |          1        0.00       99.99
                               mail |          1        0.00       99.99
                                mcy |          1        0.00       99.99
                              mopad |          1        0.00       99.99
                              motor |          3        0.00       99.99
                                mta |          1        0.00       99.99
                              mta b |          2        0.00       99.99
                              nyc a |          1        0.00       99.99
                               omni |          1        0.00       99.99
                               p/sh |          1        0.00       99.99
                                pas |          1        0.00       99.99
                              passa |          1        0.00       99.99
                               pick |          4        0.00       99.99
                              posta |          2        0.00       99.99
                              power |          1        0.00       99.99
                               rd/s |          1        0.00       99.99
                               refg |          1        0.00       99.99
                                 rv |          2        0.00       99.99
                              sanit |          2        0.00       99.99
                              schoo |          7        0.00       99.99
                              sciss |          1        0.00       99.99
                              scoot |          2        0.00       99.99
                              seagr |          1        0.00       99.99
                               self |          1        0.00       99.99
                               semi |          2        0.00       99.99
                               sgws |          1        0.00       99.99
                              spc p |          1        0.00       99.99
                               spec |          1        0.00       99.99
                              stree |          1        0.00       99.99
                               subn |          3        0.00       99.99
                                 tk |          1        0.00       99.99
                                tow |          2        0.00       99.99
                              tow t |          3        0.00       99.99
                               trac |          1        0.00       99.99
                              tract |          6        0.00       99.99
                              trail |         17        0.00       99.99
                                trk |          1        0.00       99.99
                               trlr |          1        0.00       99.99
                              truck |          9        0.00       99.99
                              uhaul |          1        0.00       99.99
                              ulili |          1        0.00       99.99
                                unk |          3        0.00       99.99
                              unkno |          4        0.00       99.99
                              ups t |          1        0.00       99.99
                               usps |          2        0.00       99.99
                              utili |          6        0.00       99.99
                                van |         75        0.01      100.00
                              van t |          1        0.00      100.00
                                vol |          1        0.00      100.00
------------------------------------+-----------------------------------
                              Total |  1,375,333      100.00
  1. Find some key words. E.g. BICYC, BICYCLE, Bike
[19]:
gen bicyclerelated = vehicletypecode1 == "BICYC" | ///
vehicletypecode1 == "BICYCLE" | ///
vehicletypecode1 == "Bike"
[41]:
tab bicyclerelated

bicyclerela |
        ted |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |  1,378,523       99.46       99.46
          1 |      7,480        0.54      100.00
------------+-----------------------------------
      Total |  1,386,003      100.00

The above method requires that vehicletypecode1 have exactly those characters within the double quotation marks. If “BICYC” were spelt in lower capitals, the command above will not capture it. Hence, we can “improve” upon the code by using lower() or upper() functions to ensure that the strings are in the right form.

[20]:
gen bicyclerelated_2 = upper(vehicletypecode1) == "BICYC" | ///
upper(vehicletypecode1) == "BICYCLE" | ///
upper(vehicletypecode1) == "BIKE"
[42]:
tab bicyclerelated_2

bicyclerela |
      ted_2 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |  1,378,523       99.46       99.46
          1 |      7,480        0.54      100.00
------------+-----------------------------------
      Total |  1,386,003      100.00

The method above is tedious because it requires that you look for every single keyword that is related to a bicycle. For example, in order to identify Minibike, you need to know that it exists in vehicletypecode1. We can use Stata’s strpos function to look within a string for a keyword. If strpos finds the keyword, the function will return the value of the position in the string. But instead of storing the position, we will use a boolean logic to identify that observation and create the dummy variable.

[22]:
gen bicyclerelated_3 = strpos(upper(vehicletypecode1),"BICYC" ) > 0 | ///
strpos(upper(vehicletypecode1),"BIKE" ) > 0
[44]:
tab bicyclerelated_3

bicyclerela |
      ted_3 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |  1,378,473       99.46       99.46
          1 |      7,530        0.54      100.00
------------+-----------------------------------
      Total |  1,386,003      100.00

Next, we can also use the screening command. This command is a little more complicated but it allows for spelling mistakes to be captured as well.

[37]:
screening ,sources(vehicletypecode1, upper) keys(BICYCLE BIKE) letters(5 3) explore(count)

             Source |   Key |      Freq. | Percent
--------------------+-------+------------+---------
   vehicletypecode1 | BICYC |       5568 |   73.84
                    |   BIK |       1973 |   26.16
--------------------+-------+------------+---------
                    | Total |       7541 |  100.00

[39]:
screening ,sources(vehicletypecode1, upper) keys(BICYCLE BIKE) letters(5 3) newcode(bicyclerelated_4, replace)

WARNING: By specifying -replace- sub-option you are overwriting the -newcode()- variable.
[45]:
tab bicyclerelated_4

bicyclerela |
      ted_4 |      Freq.     Percent        Cum.
------------+-----------------------------------
          1 |      5,568       73.84       73.84
          2 |      1,973       26.16      100.00
------------+-----------------------------------
      Total |      7,541      100.00

Explore the coodinates

[46]:
codebook latitude

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
latitude                                                                                                                                                                                                                                               LATITUDE
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                  type:  numeric (float)

                 range:  [0,43.344444]                units:  1.000e-06
         unique values:  70,056                   missing .:  236,251/1,386,003

                  mean:   40.7115
              std. dev:   .706688

           percentiles:        10%       25%       50%       75%       90%
                            40.619   40.6687   40.7229   40.7674    40.837
[47]:
codebook longitude

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
longitude                                                                                                                                                                                                                                             LONGITUDE
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                  type:  numeric (float)

                 range:  [-201.35999,0]               units:  1.000e-06
         unique values:  48,842                   missing .:  236,251/1,386,003

                  mean:  -73.9102
              std. dev:   1.72118

           percentiles:        10%       25%       50%       75%       90%
                           -74.003  -73.9782  -73.9311  -73.8678  -73.8049

When we examine the codebook outputs. It is helpful to look at the unique values and the missing. From this we know that these coordinates are not all unique and that there are missing observations in the data.

We will try to fill in the missing latitude and logitude based on other characteristics about the intersection. But first we will create a dummy variable so that we can keep track of observations with missing longitude and latitudes.

[48]:
gen mi_latlong = mi(latitude) & mi(longitude)

Now we can see the number of missing over the years

[50]:
tab mi_latlong year_SIF, column

+-------------------+
| Key               |
|-------------------|
|     frequency     |
| column percentage |
+-------------------+

           |                                   year_SIF
mi_latlong |      2012       2013       2014       2015       2016       2017       2018 |     Total
-----------+-----------------------------------------------------------------------------+----------
         0 |    85,451    171,915    172,726    182,957    136,345    212,986    187,372 | 1,149,752
           |     84.99      84.39      83.84      84.04      59.71      92.84      93.58 |     82.95
-----------+-----------------------------------------------------------------------------+----------
         1 |    15,089     31,809     33,302     34,734     92,019     16,437     12,860 |   236,250
           |     15.01      15.61      16.16      15.96      40.29       7.16       6.42 |     17.05
-----------+-----------------------------------------------------------------------------+----------
     Total |   100,540    203,724    206,028    217,691    228,364    229,423    200,232 | 1,386,002
           |    100.00     100.00     100.00     100.00     100.00     100.00     100.00 |    100.00

The column option tells stata to provide percentages by each year.

There are several key variables that we can use to determine if the intersections are the same. - borough - zipcode - onstreetname - crossstreetname

We will use Stata’s variable[_n-1] code to compare between rows of the observations. The idea is that if the borough, zipcode, onstreetname and crossstreetname of the previous row of observation is the same as the borough of the current row of observation, then the intersection has to be the same. Therefore, if the latitude and longitude are missing, we can fill it in with the non-missing latitude and longitude from the previous row.

First, we want to sort the data in order.

[51]:
sort onstreetname crossstreetname latitude longitude
[52]:
replace latitude = latitude[_n-1] if ///
mi(latitude) & ///
(borough == borough[_n-1]) & ///
(zipcode == zipcode[_n-1]) & ///
(onstreetname == onstreetname[_n-1]) & ///
(crossstreetname ==  crossstreetname[_n-1])

replace longitude = longitude[_n-1] if ///
mi(longitude) & ///
(borough == borough[_n-1]) & ///
(zipcode == zipcode[_n-1]) & ///
(onstreetname == onstreetname[_n-1]) & ///
(crossstreetname ==  crossstreetname[_n-1])

(74,517 real changes made)

(74,517 real changes made)

We managed to “back fill” quite a large number of observations! Now, we want to identify observations that still have missing latitude and longitudes.

[53]:
gen mi_latlong2 = mi(latitude) & mi(longitude)
[54]:
tab mi_latlong2 year_SIF, column

+-------------------+
| Key               |
|-------------------|
|     frequency     |
| column percentage |
+-------------------+

mi_latlong |                                   year_SIF
         2 |      2012       2013       2014       2015       2016       2017       2018 |     Total
-----------+-----------------------------------------------------------------------------+----------
         0 |    86,719    174,277    175,092    185,272    188,838    220,422    193,649 | 1,224,269
           |     86.25      85.55      84.98      85.11      82.69      96.08      96.71 |     88.33
-----------+-----------------------------------------------------------------------------+----------
         1 |    13,821     29,447     30,936     32,419     39,526      9,001      6,583 |   161,733
           |     13.75      14.45      15.02      14.89      17.31       3.92       3.29 |     11.67
-----------+-----------------------------------------------------------------------------+----------
     Total |   100,540    203,724    206,028    217,691    228,364    229,423    200,232 | 1,386,002
           |    100.00     100.00     100.00     100.00     100.00     100.00     100.00 |    100.00
[55]:
tab mi_latlong year_SIF, column

+-------------------+
| Key               |
|-------------------|
|     frequency     |
| column percentage |
+-------------------+

           |                                   year_SIF
mi_latlong |      2012       2013       2014       2015       2016       2017       2018 |     Total
-----------+-----------------------------------------------------------------------------+----------
         0 |    85,451    171,915    172,726    182,957    136,345    212,986    187,372 | 1,149,752
           |     84.99      84.39      83.84      84.04      59.71      92.84      93.58 |     82.95
-----------+-----------------------------------------------------------------------------+----------
         1 |    15,089     31,809     33,302     34,734     92,019     16,437     12,860 |   236,250
           |     15.01      15.61      16.16      15.96      40.29       7.16       6.42 |     17.05
-----------+-----------------------------------------------------------------------------+----------
     Total |   100,540    203,724    206,028    217,691    228,364    229,423    200,232 | 1,386,002
           |    100.00     100.00     100.00     100.00     100.00     100.00     100.00 |    100.00

There are other ways to identify the latitude and longitude of these missing variables through a process called Geocoding. But we will explore that another time.

For now, we will drop observations that are still missing.

[58]:
drop if mi_latlong2 == 1
(161,734 observations deleted)

There are also some observations that have coordinates that are outside the boundaries of New York City. We will drop them as well.

[59]:
drop if longitude < -74.5 | longitude > -73
drop if latitude < 40 |  latitude > 41

(607 observations deleted)

(8 observations deleted)

Since, there are many observations, I will pull out a random sample of 10% of the data to quickly visualize. In Stata, we use preserve to keep the data at that point in memory and manipulate the data to do calculations/obtain weights/collapse/etc. Then, after we obtain our numbers/graphs/etc, we will use store to “get back” to the dataset that was at the point of preserve. We typically use preserve and restore commands when collapsing data.

[61]:
preserve
sample 10
twoway scatter latitude longitude, msymbol(o) msize(tiny)
restore


(1,101,289 observations deleted)




[62]:
display "$S_DATE $S_TIME"
notes: "Cleaned $S_DATE $S_TIME"
notes list

 7 May 2019 13:59:05



_dta:
  1.  "Downloaded 9 Dec 2018 11:30:26"
  2.  "Cleaned 7 May 2019 13:59:05"
[ ]:
save "..\working_data\NYPD_Motor_Vehicle_Collisions_clean.dta",replace